from flask import Blueprint, g, current_app, jsonify, request
import jwt
import uuid
import os
from sqlalchemy import text

from database import db, to_json, User, 违约认定人工审核表, 客户表, 违约风险原因表, V_违约认定审核总信息

weiyue = Blueprint('weiyue', __name__, url_prefix='/weiyue')


@weiyue.before_request
def verify_jwt():
    try:
        token = request.form.get("token", type=str, default=None)
        if not token:
            from flask import abort
            abort(401, description='Token missing')
        
        username = jwt.decode(token, current_app.config['JWT_SECRET_KEY'], algorithms='HS256')['username']
        g.user = User.query.get(username)
        
        if not g.user:
            from flask import abort
            abort(401, description='User not found')
            
    except jwt.InvalidTokenError:
        from flask import abort
        abort(401, description='Invalid token')
    except Exception as e:
        from flask import abort
        abort(401, description='Authentication failed')


def checkCustomExist(customid):
    try:
        custom = 客户表.query.get(customid)
    except Exception as e:
        print(e)
        return {'status': f'数据库连接失败,请联系管理员!'}

    if (custom is None):
        return {'status': "不存在的客户!"}
    elif (custom.违约情况 == 1):
        return {'status': '该客户已违约, 请勿额外申请'}
    else:
        return {'status': 'success'}


def getApplyForm(ApplyFormid):
    try:
        applyForm = 违约认定人工审核表.query.get(ApplyFormid)
    except Exception as e:
        print(e)
        return {'status': f'数据库连接失败,请联系管理员!'}, None

    if (applyForm is None):
        return {'status': "不存在的申请!"}, None
    elif (applyForm.审核状态 == "审核通过" or applyForm.审核状态 == "审核未通过"):
        return {'status': '该申请已完成处理, 需要修改违约状态请使用重生功能!'}, None
    else:
        return {'status': 'success'}, applyForm


@weiyue.route('/apply', methods=['POST'])
def new():
    customid = request.form.get("customid", type=str, default=None)
    outLevel = request.form.get("outLevel", type=int, default='0')
    reasonid = request.form.get("reason", type=int, default=None)
    dangerLevel = request.form.get("dangerLevel", type=int, default='1')
    info = request.form.get("info", type=str, default=None)
    checkResult = checkCustomExist(customid)
    if not(checkResult['status'] == 'success'):
        return checkResult
    applyForm = 违约认定人工审核表(违约审核编号=os.urandom(8).hex(),客户号=customid, 违约原因编号=reasonid,
                          严重程度=dangerLevel, 认定人="", 外部最新等级=outLevel, 备注=info)
    try:
        db.session.add(applyForm)
        db.session.commit()
    except Exception as e:
        print(e)
        return {'status': f'db error'}
    return {'status': f'success'}


@weiyue.route('/verify', methods=['POST'])
def verify():
    passed = request.form.get("passed", type=str, default='')
    id = request.form.get("id", type=str, default=None)
    checkResult, applyForm = getApplyForm(id)
    if not(checkResult['status'] == 'success'):
        return checkResult
    if not(passed == "审核通过" or passed == "审核未通过"):
        return {"status": "请选择审核通过与否!"}
    setattr(applyForm, '审核状态', passed)
    setattr(applyForm, '认定人', g.user.username)
    
    # 添加调试信息
    print(f"【调试】审核状态: {passed}")
    print(f"【调试】客户号: {applyForm.客户号}")
    print(f"【调试】审核状态设置后: {applyForm.审核状态}")
    
    try:
        # db.session.update(applyForm)
        try:
            db.session.update(applyForm)
        except:
            pass
        db.session.commit()
        print(f"【调试】数据库提交成功")
        
        # 手动更新客户违约情况（作为触发器的备选方案）
        if passed == "审核通过":
            try:
                from database import 客户表
                customer = 客户表.query.get(applyForm.客户号)
                if customer:
                    customer.违约情况 = 1
                    db.session.commit()
                    print(f"【调试】手动更新客户违约情况成功: {applyForm.客户号}")
                else:
                    print(f"【调试】客户不存在: {applyForm.客户号}")
            except Exception as e:
                print(f"【调试】手动更新客户违约情况失败: {e}")
        
    except Exception as e:
        print(e)
        return {'status': f'db error'}
    return {'status': f'success'}


@weiyue.route('/custom', methods=['POST'])
def show():
    try:
        customs = 客户表.query.all()
    except Exception as e:
        print(e)
        return {'status': f'数据库连接失败,请联系管理员!'}

    return jsonify(to_json(customs))


@weiyue.route('/custom/add', methods=['POST'])
def add_customer():
    # 从前端表单获取字段
    customer_id = request.form.get('customerId', type=str, default=None)
    customer_name = request.form.get('customerName', type=str, default=None)
    gender = request.form.get('gender', type=str, default=None)
    region = request.form.get('region', type=str, default=None)
    industry = request.form.get('industry', type=str, default=None)
    phone = request.form.get('phone', type=str, default=None)
    group = request.form.get('group', type=str, default=None)

    # 基本校验
    if not all([customer_id, customer_name, gender, region, industry, phone, group]):
        return {'status': 'missing fields'}, 400

    # 检查是否已存在
    try:
        exists = 客户表.query.get(customer_id)
        if exists is not None:
            return {'status': 'customer exists'}, 400
    except Exception as e:
        print(e)
        return {'status': '数据库连接失败,请联系管理员!'}, 500

    # 创建并写入
    try:
        new_customer = 客户表(
            客户号=customer_id,
            客户名=customer_name,
            性别=gender,
            区域=region,
            行业=industry,
            联系方式=phone,
            集团=group,
            违约情况=0
        )
        db.session.add(new_customer)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        print(e)
        return {'status': 'db error'}, 500

    return {'status': 'success'}


@weiyue.route('/reason', methods=['POST'])
def reason():
    enable = request.form.get("enable", type=int, default=None)
    if enable is None:
        try:
            reasons = 违约风险原因表.query.all()
        except Exception as e:
            print(e)
            return {'status': f'数据库连接失败,请联系管理员!'}
    else:
        try:
            db.session.flush()
            reasons = 违约风险原因表.query.filter_by(是否启用=enable).all()
        except Exception as e:
            print(e)
            return {'status': f'数据库连接失败,请联系管理员!'}

    return jsonify(to_json(reasons))


@weiyue.route('/change_reason', methods=['POST'])
def cgreason():
    enable = request.form.get("enable", type=int, default=None)
    reasonid = request.form.get("reasonid", type=int, default=None)
    if enable is None:
        try:
            db.session.flush()
            reasons = 违约风险原因表.query.all()
        except Exception as e:
            print(e)
            return {'status': f'数据库连接失败,请联系管理员!'}
    else:
        try:
            reasons = 违约风险原因表.query.get(reasonid)
            setattr(reasons, '是否启用', enable)
            try:
                try:
                    db.session.update(reasons)
                except:
                    pass
                db.session.commit()
                # db.session.flush()
            except Exception as e:
                print(e)
                return {'status': f'数据库连接失败,请联系管理员!'}
        except Exception as e:
            print(e)
            return {'status': '没有这个风险'}
        return {'status': 'success'}
    return jsonify(to_json(reasons))


@weiyue.route('/records', methods=['POST'])
def showWeiyueRecords():
    passQuery = request.form.get("passed", type=str, default=None)
    print(passQuery)
    if not passQuery:
        try:
            db.session.flush()
            db.session.commit()
            records = (V_违约认定审核总信息
                       .query
                       .order_by(V_违约认定审核总信息.认定申请时间.asc())
                       .all())
        except Exception as e:
            print(e)
            return {'status': f'数据库连接失败,请联系管理员!'}
    else:
        try:
            db.session.flush()
            db.session.commit()
            records = (V_违约认定审核总信息
                       .query
                       .filter_by(审核状态=passQuery)
                       .order_by(V_违约认定审核总信息.认定申请时间.asc())
                       .all())
        except Exception as e:
            print(e)
            return {'status': f'数据库连接失败,请联系管理员!'}

    return jsonify(to_json(records))


@weiyue.after_request
def add_header(response):
    response.headers['Access-Control-Allow-Origin'] = '*'
    return response

# 添加错误处理器
@weiyue.errorhandler(401)
def unauthorized(error):
    return jsonify({'status': 'unauthorized', 'message': 'Authentication failed'}), 401

@weiyue.errorhandler(500)
def internal_error(error):
    return jsonify({'status': 'error', 'message': 'Internal server error'}), 500

@weiyue.route('/statistics/industry', methods=['POST'])
def industry_statistics():
    year = request.form.get("year", type=int, default=2024)
    
    try:
        # 获取行业违约统计数据
        breach_query = f"""
        SELECT 行业, COUNT(*) as 违约数量, MONTH(认定申请时间) as 月份
        FROM V_违约认定审核总信息 
        WHERE YEAR(认定申请时间) = {year} AND 审核状态 = '审核通过'
        GROUP BY 行业, MONTH(认定申请时间)
        ORDER BY 行业, 月份
        """
        
        # 获取行业恢复统计数据
        recovery_query = f"""
        SELECT c.行业, COUNT(*) as 恢复数量, MONTH(r.重生申请时间) as 月份
        FROM 重生人工审核表 r
        JOIN 违约认定人工审核表 w ON r.违约审核编号 = w.违约审核编号
        JOIN 客户表 c ON w.客户号 = c.客户号
        WHERE YEAR(r.重生申请时间) = {year} AND r.审核状态 = '审核通过'
        GROUP BY c.行业, MONTH(r.重生申请时间)
        ORDER BY c.行业, 月份
        """
        
        # 执行查询
        breach_result = db.session.execute(text(breach_query)).fetchall()
        recovery_result = db.session.execute(text(recovery_query)).fetchall()
        
        # 处理数据格式
        breach_data = {}
        for row in breach_result:
            industry = row[0]
            if industry not in breach_data:
                breach_data[industry] = {"total": 0, "monthly": [0]*12}
            breach_data[industry]["total"] += row[1]
            breach_data[industry]["monthly"][row[2]-1] += row[1]
        
        recovery_data = {}
        for row in recovery_result:
            industry = row[0]
            if industry not in recovery_data:
                recovery_data[industry] = {"total": 0, "monthly": [0]*12}
            recovery_data[industry]["total"] += row[1]
            recovery_data[industry]["monthly"][row[2]-1] += row[1]
            
        return jsonify({
            "success": True, 
            "data": {
                "breach": breach_data,
                "recovery": recovery_data
            }
        })
        
    except Exception as e:
        print(e)
        return jsonify({"success": False, "message": "获取行业统计数据失败"})

@weiyue.route('/statistics/region', methods=['POST'])
def region_statistics():
    year = request.form.get("year", type=int, default=2024)
    
    try:
        # 获取区域违约统计数据
        breach_query = f"""
        SELECT 区域, COUNT(*) as 违约数量, MONTH(认定申请时间) as 月份
        FROM V_违约认定审核总信息 
        WHERE YEAR(认定申请时间) = {year} AND 审核状态 = '审核通过'
        GROUP BY 区域, MONTH(认定申请时间)
        ORDER BY 区域, 月份
        """
        
        # 获取区域恢复统计数据
        recovery_query = f"""
        SELECT c.区域, COUNT(*) as 恢复数量, MONTH(r.重生申请时间) as 月份
        FROM 重生人工审核表 r
        JOIN 违约认定人工审核表 w ON r.违约审核编号 = w.违约审核编号
        JOIN 客户表 c ON w.客户号 = c.客户号
        WHERE YEAR(r.重生申请时间) = {year} AND r.审核状态 = '审核通过'
        GROUP BY c.区域, MONTH(r.重生申请时间)
        ORDER BY c.区域, 月份
        """
        
        # 执行查询
        breach_result = db.session.execute(text(breach_query)).fetchall()
        recovery_result = db.session.execute(text(recovery_query)).fetchall()
        
        # 处理数据格式
        breach_data = {}
        for row in breach_result:
            region = row[0]
            if region not in breach_data:
                breach_data[region] = {"total": 0, "monthly": [0]*12}
            breach_data[region]["total"] += row[1]
            breach_data[region]["monthly"][row[2]-1] += row[1]
        
        recovery_data = {}
        for row in recovery_result:
            region = row[0]
            if region not in recovery_data:
                recovery_data[region] = {"total": 0, "monthly": [0]*12}
            recovery_data[region]["total"] += row[1]
            recovery_data[region]["monthly"][row[2]-1] += row[1]
            
        return jsonify({
            "success": True, 
            "data": {
                "breach": breach_data,
                "recovery": recovery_data
            }
        })
        
    except Exception as e:
        print(e)
        return jsonify({"success": False, "message": "获取区域统计数据失败"})